package cn.yunhe.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import cn.yunhe.dao.DaoFactory;
import cn.yunhe.dao.INewsDao;
import cn.yunhe.entity.Admin;
import cn.yunhe.entity.Cate;
import cn.yunhe.entity.News;

public class NewsDaoImpl implements INewsDao {
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	 
	public List<News> getAllNews() {
		List<News> news = new ArrayList<News>();
		try {
			conn = DaoFactory.getConn();
			String sql = "select * from tb_news";
			pstmt = conn.prepareStatement(sql);

			rs = pstmt.executeQuery();

			while (rs.next()) {
				News n=new News(
				rs.getInt("news_id"),
				rs.getString("news_title"),
				rs.getString("news_content"),
				rs.getDate("news_ptime"),
				rs.getString("news_pic"),
				new Admin(rs.getInt("news_admin")),
				new Cate(rs.getInt("news_cate")));
				
				news.add(n);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DaoFactory.closeAll(conn, pstmt, rs);
		}

		return news;
	}

 	public boolean addNews(News news) {
 		boolean flag = false;
		try {
			conn = DaoFactory.getConn();
			String sql = "insert into scott.tb_news(news_id,news_title,news_content,news_ptime,news_pic,news_admin,news_cate) values(seq_newsid.nextval,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, news.getNewsTitle());
			pstmt.setString(2, news.getNewsContent());
			pstmt.setDate(3, new java.sql.Date(news.getNewsPtime().getTime()));
			pstmt.setString(4, news.getNewsPic());
			pstmt.setInt(5, news.getAdmin().getAdminId());
			pstmt.setInt(6, news.getCate().getCateId());
			
			
			int result = pstmt.executeUpdate();
			if (result > 0) {
				flag = true;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DaoFactory.closeAll(conn, pstmt, null);
		}

		return flag;
	}

 	public int getNewsCount() {
 		int count=0;
 		try {
			conn=DaoFactory.getConn();
			String sql="select count(news_id) from tb_news";
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			
			if(rs.next()){
				count=rs.getInt(1);
			}
			
 			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DaoFactory.closeAll(conn, pstmt, null);
		}
 		
 		
 		return count;
	}

 	public List<News> getNewsByPage(int pageSize, int currentPage) {
 		List<News> news = new ArrayList<News>();
		try {
			conn = DaoFactory.getConn();
			String sql = "select * from "
						+ "(select t.*,rownum rn from "
							+ "(select * from tb_news order by news_id desc) t "
						+ "where rownum <= "+(pageSize*currentPage)+") "
					   + "where rn >="+(pageSize*(currentPage-1)+1)+"";
			
 			
			pstmt = conn.prepareStatement(sql);

			rs = pstmt.executeQuery();

			while (rs.next()) {
				News n=new News(
				rs.getInt("news_id"),
				rs.getString("news_title"),
				rs.getString("news_content"),
				rs.getDate("news_ptime"),
				rs.getString("news_pic"),
				new Admin(rs.getInt("news_admin")),
				new Cate(rs.getInt("news_cate")));
				
				news.add(n);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DaoFactory.closeAll(conn, pstmt, rs);
		}

		return news;
	}

 	public List<News> getNewsByCateId(int cateId) {
 		List<News> news = new ArrayList<News>();
		try {
			conn = DaoFactory.getConn();
			String sql = "select * from tb_news where news_cate = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, cateId);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				News n=new News(
				rs.getInt("news_id"),
				rs.getString("news_title"),
				rs.getString("news_content"),
				rs.getDate("news_ptime"),
				rs.getString("news_pic"),
				new Admin(rs.getInt("news_admin")),
				new Cate(rs.getInt("news_cate")));
				
				news.add(n);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DaoFactory.closeAll(conn, pstmt, rs);
		}

		return news;
	}

 	public News getNewsByNewsId(int newsId) {
 		News news=null;
 		try {
			conn = DaoFactory.getConn();
			String sql = "select * from tb_news where news_id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, newsId);
			
			rs = pstmt.executeQuery();

			while (rs.next()) {
				news=new News(
				rs.getInt("news_id"),
				rs.getString("news_title"),
				rs.getString("news_content"),
				rs.getDate("news_ptime"),
				rs.getString("news_pic"),
				new Admin(rs.getInt("news_admin")),
				new Cate(rs.getInt("news_cate")));				
 			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DaoFactory.closeAll(conn, pstmt, rs);
		}

		return news;
	}

}
